package br.ufg.apkandroidufg.dados;

import java.io.InputStream;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;

import org.w3c.dom.Document;
import org.w3c.dom.NodeList;

import br.ufg.apkandroidufg.R;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
import android.widget.Toast;

public class DatabaseHelper extends SQLiteOpenHelper {
	private static final String DATABASE_NAME = "apk_ufg";

	private static final String TABLE_PESSOA = "CREATE TABLE pessoa "
			+ "( ID INTEGER PRIMARY KEY ," + " NOME TEXT ," + " EMAIL TEXT ,"
			+ " USUARIO INTEGER );";

	private static final String TABLE_ALUNO = "CREATE TABLE aluno "
			+ "( ID INTEGER PRIMARY KEY, "
			+ " FOREIGN KEY(id) REFERENCES pessoa(id) );";

	private static final String TABLE_PROFESSOR = "CREATE TABLE professor "
			+ "( ID INTEGER PRIMARY KEY, "
			+ " FOREIGN KEY(id) REFERENCES pessoa(id) );";

	private static final String TABLE_TURMA = "CREATE TABLE turma "
			+ "( ID INTEGER PRIMARY KEY, " + " NOME TEXT );";

	private static final String TABLE_MATERIA = "CREATE TABLE materia "
			+ "( ID INTEGER PRIMARY KEY, " + " NOME TEXT );";

	private Context context;

	public DatabaseHelper(Context context) {
		super(context, DATABASE_NAME, null, 2);
		this.context = context;
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL(TABLE_PESSOA);
		db.execSQL(TABLE_ALUNO);
		db.execSQL(TABLE_PROFESSOR);
		db.execSQL(TABLE_TURMA);
		db.execSQL(TABLE_MATERIA);
		String s;
		try {
			InputStream in = context.getResources().openRawResource(R.raw.sql);
			DocumentBuilder builder = DocumentBuilderFactory.newInstance()
					.newDocumentBuilder();
			Document doc = builder.parse(in, null);
			NodeList statements = doc.getElementsByTagName("statement");
			for (int i = 0; i < statements.getLength(); i++) {
				s = statements.item(i).getChildNodes().item(0).getNodeValue();
				db.execSQL(s);
			}
		} catch (Throwable t) {
			Toast.makeText(context, t.toString(), Toast.LENGTH_LONG).show();
			Log.e("CriacaoBanco", t.toString(), t);
		}
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		db.execSQL("DROP TABLE IF EXISTS aluno");
		db.execSQL("DROP TABLE IF EXISTS professor");
		db.execSQL("DROP TABLE IF EXISTS pessoa");
		db.execSQL("DROP TABLE IF EXISTS turma");
		db.execSQL("DROP TABLE IF EXISTS materia");
		onCreate(db);
	}
}
